package org.dizena.cola.starter.service;

import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.MigrationInfo;
import org.flywaydb.core.api.configuration.FluentConfiguration;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.File;
import java.net.JarURLConnection;
import java.net.URI;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ExecuteService {

    @Resource
    private DataSource dataSource;

    private static final Map<String, String> sqlTableMap = new HashMap<String, String>() {{
        put("auth", "flyway_schema_history");
        put("basic", "flyway_intelligent_schema");
        put("product", "flyway_intelligent_schema_product");
        put("analysis", "flyway_intelligent_schema_analysis");
        put("quartz", "flyway_intelligent_schema_quartz");
    }};


    private void createTable1(String tableName) {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
        sb.append("installed_rank int(11) NOT NULL,");
        sb.append("version varchar(50) DEFAULT NULL,");
        sb.append("description varchar(200) NOT NULL,");
        sb.append("type varchar(20) NOT NULL,");
        sb.append("script varchar(1000) NOT NULL,");
        sb.append("checksum int(11) DEFAULT NULL,");
        sb.append("installed_by varchar(100) NOT NULL,");
        sb.append("installed_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,");
        sb.append("execution_time int(11) NOT NULL,");
        sb.append("success tinyint(1) NOT NULL,");
        sb.append("PRIMARY KEY (installed_rank),");
        sb.append("KEY ").append(tableName).append("_s_idx (success) BLOCK_SIZE 16384 LOCAL");
        sb.append(") DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0");
        String sql = sb.toString();
        try {
            Connection connection = dataSource.getConnection();
            connection.createStatement().execute(sql);
        } catch (SQLException e) {
            log.error("Create Table Error with: {}", sql);
            log.error(e.getMessage());
        }
    }

    public void execute(String projects, String action, String version) {
        String[] ary = projects.split(",");
        for (String project : ary) {
            String tableName = sqlTableMap.get(project);
            if (tableName == null || tableName.length() == 0) {
                log.error("{} 不存在于SqlMap中！", project);
                continue;
            }
            // 脚本执行
            String location = getSqlLocation(project, action, version);

            switch (action) {
                // 备份
                case "backup":
                    backup(tableName, location);
                    break;
                // 执行
                case "migrate":
                    migrate(tableName, location);
                    break;
                // 回退
                case "undo":
                    undo(tableName, location);
                    break;
                // 备份-执行-回退-执行
                case "execution":
                    backup(tableName, location);
                    migrate(tableName, location);
                    undo(tableName, location);
                    migrate(tableName, location);
                    break;
                default:
                    log.error("action 参数异常！");
                    return;
            }

        }
    }

    private void undo(String tableName, String location) {
        log.info("开始undo...");
        File dir = new File(location.replace("filesystem:", ""));
        List<String> sqlPaths = Arrays.stream(dir.listFiles())
                .sorted(Comparator.comparing(File::getName))
                .map(file -> file.getAbsolutePath())
                .collect(Collectors.toList());
        List<String> sqlNames = Arrays.stream(dir.listFiles())
                .sorted(Comparator.comparing(File::getName))
                .map(file -> file.getName())
                .collect(Collectors.toList());
        Collections.reverse(sqlPaths);
        Collections.reverse(sqlNames);
        for (int i = 0; i < sqlNames.size(); i++) {
            String filePath = sqlPaths.get(i);
            String script = sqlNames.get(i);
            boolean flag = dataSourceDo(filePath);
            if (flag) {
                String sql = "delete from " + tableName + " where script='" + script + "'";
                jdbcDo(sql);
                log.info("undo {} success", script);
            } else {
                log.error("undo {} error", script);
                break;
            }
        }
        log.info("结束undo...");
    }

    private void backup(String tableName, String location) {
        tableName = tableName + "_backup";
        flywayDo(tableName, location);
    }

    private void migrate(String tableName, String location) {
        flywayDo(tableName, location);
    }

    private void flywayDo(String tableName, String location) {
        // 计算迁移sql脚本路径
        try {
            // 执行迁移sql脚本
            log.info("sql脚本执行前准备...");
            FluentConfiguration configure = Flyway.configure();
            configure.dataSource(dataSource);
            log.info("sql脚本执行目录：{}", location);
            configure.locations(location);
            configure.table(tableName);
            configure.baselineOnMigrate(true);
            configure.placeholderReplacement(false);
            Flyway flyway = configure.load();
            flyway.repair();
            MigrationInfo[] beforeInfo = flyway.info().applied();
            log.info("开始执行sql脚本...");
            flyway.migrate();

            // 日志记录执行成功记得sql脚本
            MigrationInfo[] afterInfo = flyway.info().applied();
            if (null != afterInfo && afterInfo.length > 0) {
                List<String> successNames = new ArrayList<>();
                Map<String, MigrationInfo> infoMap = new HashMap<>();
                for (MigrationInfo info : beforeInfo) {
                    infoMap.put(info.getScript(), info);
                }
                for (MigrationInfo info : afterInfo) {
                    String script = info.getScript();
                    if (!infoMap.containsKey(script) && !"BASELINE".equals(info.getType().name())) {
                        successNames.add(script);
                    }
                }
                log.info(successNames.toString());
            }
        } catch (Exception e) {
            log.error("执行迁移sql脚本失败！", e);
        }
    }

    /**
     * 获取项目文件夹内容
     */
    private String getSqlLocation(String project, String action, String version) {
        String sqlPath = System.getProperty("user.dir") + File.separator + "db" + File.separator + project;
        return "filesystem:" + sqlPath + File.separator + action + File.separator + version;
    }

    private boolean jdbcDo(String sql) {
        try {
            Connection connection = dataSource.getConnection();
            return connection.createStatement().execute(sql);
        } catch (Exception e) {
            log.error("jdbcDo error {}", e.getMessage());
        }
        return false;
    }

    private boolean dataSourceDo(String sqlFilePath) {
        try {
            org.springframework.core.io.Resource resource = new FileSystemResource(sqlFilePath);
            ResourceDatabasePopulator popular = new ResourceDatabasePopulator(resource);
            popular.execute(dataSource);
            return true;
        } catch (Exception e) {
            log.error("dataSourceDo error {}", e.getMessage());
        }
        return false;
    }

    public static List<String> getAppFileResource(String path) {
        List<String> list = new ArrayList<>();
        try {
            ClassLoader classLoader = ExecuteService.class.getClassLoader();
            Enumeration<URL> resources = classLoader.getResources(path);
            while (resources.hasMoreElements()) {
                URL url = resources.nextElement();
                if ("jar".equals(url.getProtocol())) {
                    JarURLConnection conn = (JarURLConnection) url.openConnection();
                    try (JarFile jarFile = conn.getJarFile()) {
                        Enumeration<JarEntry> entries = jarFile.entries();
                        while (entries.hasMoreElements()) {
                            JarEntry jarEntry = entries.nextElement();
                            String name = jarEntry.getName();
                            if (name.startsWith(path) && !jarEntry.isDirectory()) {
                                list.add(name);
                                // list.add("classpath:"+name);
                            }
                        }
                    }
                } else {
                    URI uri = url.toURI();
                    File dir = new File(uri);
                    File[] subDirs = dir.listFiles(File::isFile);
                    for (File subDir : subDirs) {
                        list.add(path + "/" + subDir.getName());
                    }
                }
            }
        } catch (Exception e) {

        }
        return list;
    }

}
